import pymysql

# 连接mysql
connection = pymysql.connect(
         host='127.0.0.1',       # 本地主机
        port=3306,              # 默认端口
        user='root',            # 用户名
        password='12345678',      # 密码
        database="player_data"        # 数据库名称
    )


# 读取result_list中的全部数据
def read_db():

    result_list = []

    # 创建光标对象
    cursor = connection.cursor()

    # 執行查詢語句
    query = 'SELECT * FROM result_list'

    cursor.execute(query)

    # 获取查询结果
    result = cursor.fetchall()

    # 把结果放进list中
    for row in result:
        result_list.append(row)

    # 对list按分数由高至低进行排序
    sorted_data = sorted(result_list, key=lambda x: int(x[2]), reverse=True)

    # 关闭光标对象
    cursor.close()

    # 返回已排序列表
    return sorted_data
def print_all_read():
    read_list = read_db()
    for player_info in read_list:
        print("%8s  %4s"%(player_info[1],player_info[2]))

def readf_top12_to_str():
    ans = []
    read_list = read_db()
    for i in range(12):
        ans.append("%10s  %8s"%(read_list[i][1], read_list[i][2]))
    res = '\n'.join(ans)
    return res
    

def update_data(id, result):

    # 创建光标对象
    cursor = connection.cursor()

    # 执行插入语句
    query = "UPDATE result_list SET Result = %s WHERE ID = %s"
    value = (result, id)

    cursor.execute(query, value)

    connection.commit()

    # 关闭光标对象
    cursor.close()

    # 更新提醒
    print("updated")

# def bigger_update(id, result):

# if __name__ == '__main__':
#     # update_data(3, 100)
#     print(readf_top5_to_str())
        
#     connection.close()

def get_score_by_id(player_id):
    # 创建光标对象
    cursor = connection.cursor()

    # 执行查询语句
    query = "SELECT Result FROM result_list WHERE ID = %s"
    value = (player_id,)
    cursor.execute(query, value)

    # 获取查询结果
    result = cursor.fetchone()

    # 关闭光标对象
    cursor.close()

    if result:
        return result[0]  # 返回查询到的分数
    else:
        return None  # 如果未找到对应的id，返回None或其他适当的值

# 示例用法
# player_id = 3
# score = get_score_by_id(player_id)
# if score is not None:
#     print(f"Score for player {player_id}: {score}")
# else:
#     print(f"Player with ID {player_id} not found.")

def bigger_update(id, result):
    old_result = get_score_by_id(id)
    if int(result) > int(old_result):
        update_data(id, result)
    

print_all_read()